/*
 * Name: DB.cpp
 *
 * Created on: May 3, 2011
 * Author: Siddhi Mittal
 * Description: Wrapper class for the Database
 */

#include <iostream>
#include <sstream>
#include <string>
#include <stdio.h>
#include <stdlib.h>
#include "DB.h"

using namespace std;

//Constructor
DB::DB() {
	db = new Database("Database.sqlite");
}

//Creates the tables
void DB::createTable() {
	db->query("CREATE TABLE URL (url TEXT ,counterUp INTEGER, counterDown INTEGER, category TEXT);");
	db->query("CREATE TABLE COMMENTS (url TEXT, comment TEXT);");
}

//Insert a URL along with values of counterUp and counterDown.
void DB::insertURL(char* url, char* category, int counterUp, int counterDown ) {

	string add;	

	//Checking whether the URL exists and if it does then don't do anything
	char *temp = new char[200];
	strcpy(temp, "SELECT u.url FROM URL u WHERE u.url='");
	strcat(temp, url);
	strcat(temp,"';");
	vector<vector<string> > result = db->query(temp);
	for(vector<vector<string> >::iterator it = result.begin(); it < result.end(); ++it)
	{
			vector<string> row = *it;
			//cout << "Values: (URL=" << row.at(0) << " )" << endl;
			add = row.at(0);
	}	

	//Only if the URL wasn't present before does it add it to the database
	if(add.empty())
	{
		//cout<<"I'm doing this"; - check to see if this is executed 
		//Inserting the URL if it doesn't exist
		char *s = new char[100];
		char *Up = new char[100];
		char *Down = new char[100];
		strcpy(s, "INSERT INTO URL VALUES('");
		strcat(s, url);
		strcat(s,"', ");
		sprintf(Up, "%d", counterUp);
		strcat(s, Up);
		strcat(s,", ");	
		sprintf(Down, "%d", counterDown);
		strcat(s, Down);
		strcat(s,", '");
		strcat(s, category);
		strcat(s,"');");
	
		//cout <<endl<<"This is the string: "<<s<<endl; //test
		db->query(s);
	}
}

//Increases the counter of the given URL by 1
void DB::incCounter(char* url) {
	char *s = new char[100];
	char *counter = new char[100];
	int cUP = 0;

	cUP = getCounterUp(url);
	cUP++;
	char temp[10];
	strcpy(s,"UPDATE URL SET counterUp = ");
	sprintf(temp, "%d", cUP);	
	strcat(s, temp);
	strcat(s, " WHERE url = '");
	strcat(s, url);
	strcat(s,"';");

	//cout <<endl<<"This is the string: "<<s<<endl; //test

	vector<vector<string> > result = db->query(s);
}

//Dncreases the counter of teh given URL by 1
void DB::decCounter(char* url) {
	char *s = new char[100];
	char *counter = new char[100];
	int cDown = 0;

	cDown = getCounterDown(url);
	cDown--;
	cout <<"Counter down = " << cDown <<endl;
	char temp[10];
	strcpy(s,"UPDATE URL SET counterDown = ");
	sprintf(temp, "%d", cDown);	
	strcat(s, temp);
	strcat(s, " WHERE url = '");
	strcat(s, url);
	strcat(s,"';");

	//cout <<endl<<"This is the string: "<<s<<endl; //test

	vector<vector<string> > result = db->query(s);
}

//Returns the + counter for a given URL
int DB::getCounterUp (char* url) {
	int cUP = 0;
	char *s = new char[100];
	strcpy(s,"SELECT counterUp FROM URL u WHERE u.url = '");
	strcat(s, url);
	strcat(s,"';");
	vector<vector<string> > result = db->query(s);
	for(vector<vector<string> >::iterator it = result.begin(); it < result.end(); ++it)
	{
			vector<string> row = *it;
			//cout << "Values: (COUNTERUP=" << row.at(0) << " )" << endl;
			string a = row.at(0);
			char *up;
			up = &a[0];
			cUP = cUP + atol(up);
			//cout<<"counterUP:" << cUP<<endl;
	}	
	
	//cout <<endl<<"This is the string: "<<s<<endl; //test

	return cUP;
}

//Returns the - counter for a given URL
int DB::getCounterDown(char* url) {
	int cDown = 0;
	char *s = new char[100];
	strcpy(s,"SELECT counterDown FROM URL u WHERE u.url = '");
	strcat(s, url);
	strcat(s,"';");
	vector<vector<string> > result = db->query(s);
	for(vector<vector<string> >::iterator it = result.begin(); it < result.end(); ++it)
	{
			vector<string> row = *it;
			//cout << "Values: (COUNTERDOWN "<< row.at(0) <<" )" << endl;
			string a = row.at(0);
			char *down;
			down = &a[0];
			cDown = cDown + atol(down);
			//cout<<"counterDown:"<<cDown<<endl;
	}	
	
	//cout <<endl<<"This is the string: "<<s<<endl; //test

	return cDown;
}

//Returns the category of the given URL
char* DB::getCategory(char* url) {
	char* s = new char[1500];
	char* category = new char[1500];
	strcpy(s, "SELECT category from URL u WHERE u.url = '");
	strcat(s,url);
	strcat(s, "';");

	//cout <<"Get category string s is:" <<s<<endl;
	vector<vector<string> > result = db->query(s);
	for(vector<vector<string> >::iterator it = result.begin(); it < result.end(); ++it)
	{
			vector<string> row = *it;
			cout << "Values: (CATEGORY "<< row.at(0) <<" )" << endl;
			string a = row.at(0);
			category = &a[0];
			//cout <<"The category inserted is:"<<category;
	}	
	return category;
}

//Adds a comment to the specific URL
void DB::addComment(char* url, char* comment) {
	char *s = new char[1500];
	strcpy(s, "INSERT INTO COMMENTS VALUES('");
	strcat(s, url);
	strcat(s,"' , '");
	strcat(s, comment);
	strcat(s,"');");

	//cout <<endl<<"This is the string: "<<s<<endl; //test
	db->query(s);
}

//Returns all the URLs
vector<vector<string> > DB::getURLs(char* url) {
	char *s = new char[200];
	strcpy(s, "SELECT u.url FROM URL ");

	//cout <<endl<<"This is the string: "<<s<<endl; //test
	vector<vector<string> > result = db->query(s);
	
	return result;
}

//Returns all the comments of the specific URL
vector<vector<string> > DB::getComments(char* url) {
	char *s = new char[200];
	strcpy(s, "SELECT c.url, c.comment FROM COMMENTS c, URL u WHERE u.url=c.url AND u.url='");
	strcat(s, url);
	strcat(s,"';");
	
	//cout <<endl<<"This is the string: "<<s<<endl; //test
	vector<vector<string> > result = db->query(s);
	
	return result;
}

//Test print
void DB::printValues(vector<vector<string> > result) {
		for(vector<vector<string> >::iterator it = result.begin(); it < result.end(); ++it)
		{
			vector<string> row = *it;
			//cout << "Values: (NAME=" << row.at(0) << ", UP=" << row.at(1) << endl;
			cout << "Values: (URL=" << row.at(0) << " COMMENT= " << row.at(1) <<  ")" << endl;
		}
}

//Closes the connection to the DB
void DB::closeDB() {
	db->close();
}

//Deconstructor
DB::~DB() {
	delete db;
}

